const sqlite3 = require("sqlite3").verbose();
const DATA = require("./Data.cjs");
const { promisify } = require("util");
const bcrypt = require("bcrypt");
const { splitNumId } = require("./base.cjs");
//config
const DataBase = ".sqlite.db";
//const DataBase = "sqlite2.db";

class Sqlite {
  constructor() {
    this.db = new sqlite3.Database(DataBase);
    // simplefy to return a new promise
    this.dbRunAsync = promisify(this.db.run.bind(this.db));
    this.re = "unknow";
    this.log = true;
  }

  // 打印消息，返回结果
  message(err, message) {
    let msg = "";
    let re = false;
    if (err) {
      msg = `Error: ${message}:`;
      msg += err;
      re = false;
    } else {
      msg = `ok: ${message}.`;
      re = true;
    }
    //if (this.log) console.log(msg);
    if (!re) console.log(msg);
    return re;
  }

  // 创建表 create table --store structure of tableName: xxx_data
  createTable(tableName, tableSchema = DATA.schemaPropMap) {
    //const tableSchema = this.createTableSchema();
    let sql = `CREATE TABLE IF NOT EXISTS ${tableName} (`;
    //let columnSql = "id INTEGER PRIMARY KEY AUTOINCREMENT, ";
    let columnSql = "";
    for (let key in tableSchema) {
      columnSql += `${key} ${tableSchema[key]}, `;
    }
    sql += columnSql;
    sql += ")";
    sql = sql.replace(", )", " )");

    //console.log("create table:", tableName, columnSql);
    console.log(`run: ${sql} `);
    return new Promise((resolve, reject) => {
      this.db.run(sql, (err) => {
        var re = this.message(err, "create table " + tableName);
        re ? resolve(re) : reject(err);
      });
    });
  }

  //create table for xxx_data.  --actually store data
  async createTable_data(data) {
    try {
      const schema = {};
      let type;
      //id first ,1 2 3 ...
      data.data.map((obj) => {
        type = splitNumId(obj.id).id;
        schema[obj.id] = DATA.schemaMap[type] || "TEXT";
      });
      schema["id"] = DATA.schemaMap.id;
      //console.log(`schema of ${data.tableName}_data: `, { ...schema });
      await this.createTable(data.tableName + "_data", schema);
      // let table = await this.getTable(data.tableName + "_data");
      // console.log(table);
    } catch (e) {
      throw e;
    }
  }

  //get table info
  getTable(tableName) {
    return new Promise((resolve, reject) => {
      this.db.get(`PRAGMA table_info(${tableName})`, (err, row) => {
        //console.log(row);
        var re = this.message(err, "get table info for: " + tableName);
        re ? resolve(row) : reject(err);
      });
    });
  }

  //get tableNames
  getTableNames() {
    return new Promise((resolve, reject) => {
      this.db.all(
        "SELECT name FROM sqlite_master WHERE type='table'",
        (err, rows) => {
          var re = this.message(err, "get table names");
          re ? resolve(rows.map((row) => row.name)) : reject(err);
        },
      );
    });
  }

  // make data more readable --change id to name --{text100: value} => {name: value}
  tableDataMap(data, tableInfo) {
    const infoMap = {};
    tableInfo.map((obj) => {
      infoMap[obj.name] = obj.id;
    });
    const userinfo = {};
    for (let feild in infoMap) {
      userinfo[feild] = data[infoMap[feild]];
    }
    return userinfo;
  }

  async getTableDatas(tableName) {
    //console.log("table datas:");

    // 1. get datas
    const datas = await this.queryTable({
      tableName: tableName + "_data",
    });
    //console.log(datas);
    //
    // 2.get schemas
    const schemas = await this.queryTable({
      tableName: tableName,
    });
    //console.log(schemas);
    //
    // 3. return new object, data:{id:{name:[value1,value2], otherProp:value}}
    const data = {};
    //console.log(schemas[0]);
    schemas.map((schema) => {
      const dataProp = {};
      const values = datas.map((obj) => obj[schema.id]);
      for (let prop in schema) {
        if (prop === "name") {
          dataProp["values"] = values;
        }

        dataProp[prop] = schema[prop];
      }
      data[schema.id] = dataProp;
    });
    //console.log(data);
    return data;
  }

  // delete
  deleteTable(tableName) {
    return new Promise((resolve, reject) => {
      this.db.run(`DROP TABLE ${tableName}`, (err) => {
        var re = this.message(err, "delete table " + tableName);
        re ? resolve(re) : reject(err);
      });
    });
  }

  insertData(tableName, data) {
    // 使用占位符 ? 来防止 SQL 注入攻击
    const columns = Object.keys(data).join(", "); // 获取属性名，用于构建 SQL 语句
    const values = Object.values(data); // 获取属性值，用于构建参数数组
    const placeholders = Array(values.length).fill("?").join(", "); // 生成与值数量相等的占位符字符串
    const sql = `INSERT INTO ${tableName} (${columns}) VALUES (${placeholders})`;
    console.log(`run: ${sql}`);

    return new Promise((resolve, reject) => {
      this.db.run(sql, values, (err) => {
        const re = this.message(err, "insert data to " + tableName);
        re ? resolve(re) : reject(err);
      });
    });
  }

  //  data => standard schema
  //  input:[data]
  dataStd(datas, schemas) {
    // here we cannot modify the original data, if wo do that we cause the wrong sequence
    //
    // 1. get all schemas of data
    if (!schemas) {
      console.error("warning !!! insertData: schemas not provided!");
      //return;
      schemas = Object.keys(DATA.schemaPropMap);
    }
    // 2. set data by order
    const newData = [];
    datas.map((data) => {
      const tmp = {};
      schemas.map((key) => {
        tmp[key] = data[key] || null;
      }),
        newData.push(tmp);
    });
    // 3.
    return newData;
  }

  // crypt data if tableName is xxx_data
  async cryptData(data) {
    const regex = /_data$/;
    if (regex.test(data.tableName)) {
      // 1. getSchema info , we need know which id need be crypted
      const schemas = await this.queryTable({
        tableName: data.tableName.replace("_data", ""),
      });
      // 2. change to object
      const schemasCrypt = {};
      schemas.map((schema) => (schemasCrypt[schema.id] = schema.crypt));
      // console.log(schemasCrypt);
      // change value
      data.data.map((row, index) => {
        // data.data:[{id1: value1, id2:value2},...]
        for (let id in row) {
          // when value is not empty and set need crypted
          if (schemasCrypt[id] && row[id]) {
            //        console.log(index, ": need crypt:" + id);
            row[id] = bcrypt.hashSync(row[id], 10);
          }
        }
      });
    }
  }

  // 插入数据
  async insertManyData(data) {
    // 1.ensure data is ordered and not partially
    data.data = this.dataStd(data.data, data.schemas);
    //console.log("data is:", { ...data });

    // 2.构建占位符部分 (?,?), (?,?), ...
    const placeholders = data.data
      .map(
        (obj) =>
          "(" + Array(Object.keys(obj).length).fill("?").join(", ") + ")",
      )
      .join(", ");

    // 3.crypt data if tableName is xxx_data
    await this.cryptData(data);

    // 4.构建值部分
    const values = data.data.reduce(
      (acc, obj) => acc.concat(Object.values(obj)),
      [],
    );
    // console.log("v and p");
    // console.log(values);
    // console.log(placeholders);
    // 使用占位符 ? 来防止 SQL 注入攻击
    //
    // 5.create columns and sql
    const columns = Object.keys(data.data[0]).join(", "); // 获取属性名，用于构建 SQL 语句
    const sql = `INSERT INTO ${data.tableName} (${columns}) VALUES ${placeholders}`;
    console.log(`run: ${sql} `);
    //console.log(await this.getTable(data.tableName));

    // 6.run
    return new Promise((resolve, reject) => {
      this.db.run(sql, values, (err) => {
        const re = this.message(
          err,
          `insert many data to ${data.tableName}: ` + data.data.length,
        );
        re ? resolve(re) : reject(err);
      });
    });
  }

  // 查询数据
  queryTable({ tableName, select = "*", where = true, params = [] }) {
    return new Promise((resolve, reject) => {
      console.log(
        `run: [select ${select} from ${tableName} where ${where}, params:[${params}]]`,
      );
      this.db.all(
        // `SELECT ${select} FROM ${tableName} ${where}`,
        `SELECT ${select} FROM ${tableName} WHERE ${where}`,
        params,
        (err, rows) => {
          const re = this.message(
            err,
            `query info from ${tableName}: ` + rows?.length,
          );
          re ? resolve(rows) : reject(err);
        },
      );
    });
  }

  // 更新数据
  // updateData('table1', 'col1 = "2"', "col1 > '10'")
  // updateData('a', 'col1=?, col2=?', 'col1<? OR col2<?,[1,2,0,0])
  // params 不传，则默认设置为NULL
  updateData({ tableName, set, where, params = [] }) {
    // 构建 UPDATE 语句
    const updateQuery = `UPDATE ${tableName} SET ${set} WHERE ${where}`;

    // 执行更新语句
    return new Promise((resolve, reject) => {
      this.db.run(updateQuery, params, function (err) {
        if (err) {
          console.error(`Error: updating data in ${tableName}: ${err.message}`);
        } else {
          console.log(`ok: Rows updated in ${tableName}: ${this.changes}`);
        }
        err ? reject(err) : resolve(this.changes);
      });
    });
  }

  // 删除数据
  deleteData({ tableName, where = true, params = [] }) {
    // 构建 DELETE 语句
    const deleteQuery = `DELETE FROM ${tableName} WHERE ${where}`;
    console.log(`run ${deleteQuery}`);

    return new Promise((resolve, reject) => {
      // 执行删除语句，使用参数绑定
      this.db.run(deleteQuery, params, function (err) {
        if (err) {
          console.error(
            `Error: deleting data from ${tableName}: ${err.message}`,
          );
        } else {
          console.log(`ok: Rows deleted from ${tableName}: ${this.changes}`);
        }
        err ? reject(err) : resolve(this.changes);
      });
    });
  }

  async renameTable(oldTableName, newTableName) {
    try {
      await this.dbRunAsync("PRAGMA foreign_keys=off");
      await this.dbRunAsync(
        `ALTER TABLE ${oldTableName} RENAME TO ${newTableName}`,
      );
      await this.dbRunAsync("PRAGMA foreign_keys=on");
      console.log("ok: Table renamed.");
      console.log(`${oldTableName}  -->  ${newTableName}`);
    } catch (err) {
      console.error("rename failed:", err);
      throw err; // 抛出异常以便外部捕获
    }
  }

  close() {
    return new Promise((resolve, reject) => {
      this.db.close((err) => {
        const re = this.message(err, "close database");
        re ? resolve(re) : reject(err);
        //if (re) resolve(re);
        //resolve(re);
      });
    });
  }
}

const data1 = {
  tableName: "new",
  schemas: ["name", "id", "value", "placeholder", "crypt"],
  data: [
    {
      name: "myname is crypted",
      id: "text100",
      value: "I am text",
      placeholder: "show a text",
      crypt: true,
    },
    {
      name: "myname2 is a normal name",
      id: "box101",
      value: "I am box",
      placeholder: "show a box",
    },
  ],
};
const data1_data = {
  tableName: "new_data",
  schemas: ["text100", "box101"],
  data: [
    {
      text100: "just a text",
    },
    {
      box101: "just a box.",
    },
  ],
};

//run function just simply show you how to use
async function run() {
  const DB = new Sqlite();
  // 0.delete table
  try {
    // delete table , this is reset tableStruct of table_data
    await DB.deleteTable(data1.tableName);
    // delete table data, this is reset all data of table_data
    await DB.deleteTable(data1_data.tableName);
  } catch (e) {
    console.error(`warning: table not found. ${e}`);
  }
  // 1.create table structure
  await DB.createTable(data1.tableName);
  await DB.insertManyData(data1);
  // 2.create table_data structure
  await DB.createTable_data(data1);
  // 3.insert data to table_data
  await DB.insertManyData(data1_data);
  // 4.use data, query or delet or update
  let table;
  let table_data;
  let ordered_data;
  // rows = DB.queryTable({
  //   tableName: data1_data.tableName,
  //   select: "*",
  //   where: "true",
  //   params: [],
  // });
  // below is the same, but more simple
  // 1. print table structure
  table = await DB.queryTable(data1);
  //console.log(table);
  // 2. print table data
  table_data = await DB.queryTable(data1_data);
  //console.log(table_data);
  // 3. show ordered data
  ordered_data = await DB.getTableDatas(data1.tableName);
  console.log(ordered_data);
  DB.close();
}

//run();

module.exports = Sqlite;

//export default Sqlite;
